![]() |
Java Database Programming with JDBC
by Pratik Patel Coriolis, The Coriolis Group ISBN: 1576100561 Pub Date: 10/01/96 |
Previous | Table of Contents | Next |
SQLthe language of database. This chapters primary purpose is to serve as a primer on this data sublanguage. Although it would be impossible for me to cover the intricacies of SQL in just one chapter, I do intend to give you a solid introduction that well build on in the remainder of this book. Because the JDBC requires that drivers support the ANSI SQL-92 standard to be JDBC compliant, Ill be basing this chapter on that standard. SQL-92, which Ill refer to as SQL, is based on the relational model of database management proposed in 1970 by Dr. E.F. Codd; over time, SQL evolved into the full-featured language it is today, and it continues to evolve with our ever-changing needs.
A JDBC driver doesnt absolutely have to be SQL-92 compliant. The JDBC specification states the following: In order to pass JDBC compliance tests and to be called JDBC compliant, we require that a driver support at least ANSI SQL-92 Entry Level. This requirement is clearly not possible with drivers for legacy database management systems (DBMS). The driver in these cases will not implement all of the functions of a compliant driver. In Chapter 10, Writing JDBC Drivers, we develop the basics of a JDBC driver that implements only some of the features of SQL, but is a JDBC driver nonetheless.
Well start our exploration of SQL by discussing the relational model, the basis for SQL. Then well cover the essentials of building data tables using SQL. Finally, well go into the manipulation and extraction of the data from a datasource.
Although SQL is based on the relational model, it is not a rigid implementation of it. In this section, well discuss the relational model as it pertains to SQL so we do not obfuscate our discussion of this standard, which is central to the JDBC specification. As part of its specification, the SQL-92 standard includes the definition of data types. Well cover these data types, and how to map to Java, in Chapter 6, SQL Data Types in Java and the ORM.
The basic units in SQL are tables, columns, and rows. So where does the relational model fit into the SQL units? Strictly speaking, in terms of the relation model, the relation is mapped in the table: It provides a way to relate the data contained within the table in a simple manner. A column represents a data element present in a table, while a row represents an instance of a record, or entry, in a table. Each row contains one specific value for each of the columns; a value can be blank or undefined and still be considered valid. The table can be visualized, you guessed it, as a matrix, with the columns being the vertical fields and the rows being the horizontal fields. Figure 2.1 shows an example table that can be used to store information about a companys employees.
Figure 2.1 An SQL table.
Before we push on, there are some syntax rules you need to be aware of:
A word of caution: While the keywords are not case sensitive, the string values that are stored as data in a table do preserve case, as you would expect. Keep this in mind when doing string comparisons in queries.
Though you can stick all of your data into a single table, it doesnt make sense logically to do this all the time. For example, in our EMPLOYEE table shown previously, we could add information about company departments; however, the purpose of the EMPLOYEE table is to store data on the employees. The solution is for us to create another table, called DEPARTMENT, which will contain information about the specific departments in the company. To associate an employee with a department, we can simply add a column to the EMPLOYEE table that contains the department name or number. Now that we have employees and departments neatly contained, we can add another table, called PROJECT, to keep track of the projects each employee is involved in. Figure 2.2 shows our tables.
Figure 2.2 The EMPLOYEE, DEPARTMENT, and PROJECT tables track employees by department and project.
Now that you understand how to logically separate your data, its time to take our model one step higher and introduce you to the schema/catalog relationship. The schema is a higher-level container that is defined as a collection of zero or more tables, where a table belongs to exactly one schema. In the same way, a catalog can contain zero or more schemas. This abstract is a necessary part of a robust relational database management system (RDBMS). The primary reason is access control: It facilitates who can read a table, who can change a table, and even who can create or destroy tables. Figure 2.3 demonstrates this point nicely. Here we have added another table, called CONFIDENTIAL. It contains the home address, home phone number, and salary of each employee. This information needs to belong in a separate schema so that anyone who is not in payroll cannot access the data, while allowing those in marketing to get the necessary data to do their job.
Figure 2.3 The table, schema, and catalog relationship allows you to limit access to confidential information.
Previous | Table of Contents | Next |